package cn.tedu;

import org.junit.Test;

import javax.annotation.PostConstruct;
import java.sql.*;

public class Demo03 {

    @Test  //报错 alt+回车 选择 jUnit4
    public void insert() throws SQLException {
        System.out.println("insert");
        //1.获取数据库连接
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?" +
                "characterEncoding=utf8&serverTimezone=Asia/Shanghai", "root","root");
        //2.创建执行SQL语句的对象
        Statement s=conn.createStatement();
        //3.执行SQL  execute的意思是执行语句
        s.executeUpdate("insert into emp(name) values('Tom')");
        //4.关闭资源
        conn.close();

    }
    @Test
    public void delete() throws SQLException {
        System.out.println("delete");
        //1.获取数据库连接
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?" +
                "characterEncoding=utf8&serverTimezone=Asia/Shanghai", "root","root");
        //2.创建执行SQL语句的对象
        Statement s=conn.createStatement();
        //3.执行SQL  execute的意思是执行语句
        s.executeUpdate("delete from emp where name='Jerry'");
        //4.关闭资源
        conn.close();
    }
    @Test
    public void update() throws SQLException {
        System.out.println("update");
        //1.获取数据库连接
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?" +
                "characterEncoding=utf8&serverTimezone=Asia/Shanghai", "root","root");
        //2.创建执行SQL语句的对象
        Statement s=conn.createStatement();
        //3.执行SQL  execute的意思是执行语句
        s.executeUpdate("update emp set name='Jerry' where name='Tom'");
        //4.关闭资源
        conn.close();
    }
    @Test
    public void select() throws SQLException {
        System.out.println("select");
        //1.获取数据库连接
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?" +
                "characterEncoding=utf8&serverTimezone=Asia/Shanghai", "root","root");
        //2.创建执行SQL语句的对象
        Statement s=conn.createStatement();
        //执行查询SQL语句,返回值为结果集对象 里面装着查询回来的结果
        ResultSet rs=
                s.executeQuery("select name,job,sal from emp");
        //便利结果,next下一个,让游标下移,返回值标识是否有下一条数据
        while (rs.next()){
            //从结果集对象中获取数据
            //1.通过字段名获取
            /*
            String name=rs.getString("name");
            String job=rs.getString("job");
            double sal=rs.getDouble("sal");
            */
            //2.通过字段位置获取
            String name=rs.getString(1);
            String job=rs.getString(2);
            double sal=rs.getDouble(3);
            System.out.println(name+":"+job+":"+sal);

        }
        //关闭资源
        conn.close();

    }
}
